#!/usr/bin/env python3
"""
简单的MySQL数据库查询工具
"""

import sys
import pymysql
import pymysql.cursors
from datetime import datetime


class SimpleDBQuery:
    """简单的数据库查询工具类"""
    
    def __init__(self, host='10.141.194.72', port=23306, user='viu_root', password='myviu_4359', database='d_cgboss_info'):
        """初始化数据库连接参数"""
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.conn = None
    
    def connect(self):
        """连接数据库"""
        try:
            self.conn = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )
            print(f"连接到 {self.host}:{self.port}/{self.database} 成功!")
            return True
        except Exception as e:
            print(f"数据库连接失败: {str(e)}")
            return False
    
    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            self.conn = None
    
    def query(self, sql, params=None, print_result=True):
        """
        执行SQL查询
        
        Args:
            sql: SQL查询语句
            params: 查询参数（元组或字典）
            print_result: 是否打印结果
            
        Returns:
            查询结果列表
        """
        # 如果未连接，先连接
        if not self.conn:
            if not self.connect():
                return None
        
        try:
            print(f"执行查询: {sql}")
            with self.conn.cursor() as cursor:
                cursor.execute(sql, params or ())
                results = cursor.fetchall()
            
            if print_result:
                self._print_results(results)
            
            return results
        except Exception as e:
            print(f"查询执行失败: {str(e)}")
            return None
    
    def _format_value(self, val):
        """格式化值以便于显示"""
        if val is None:
            return "NULL"
        elif isinstance(val, datetime):
            return val.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(val, (int, float)):
            return str(val)
        else:
            return str(val)
    
    def _print_results(self, results):
        """直接打印查询结果"""
        if not results:
            print("查询没有返回结果。")
            return
        
        row_count = len(results)
        print(f"查询结果 ({row_count}行):")
        
        # 直接打印每行结果
        row_limit = 100  # 最多显示100行
        for i, row in enumerate(results[:row_limit]):
            print(f"\n--- 第 {i+1} 行 ---")
            for key, value in row.items():
                print(f"{key}: {self._format_value(value)}")
        
        # 如果结果超过显示限制，显示提示
        if row_count > row_limit:
            print(f"\n(结果过多，仅显示前{row_limit}行，共{row_count}行)")
    
    def execute_update(self, sql, params=None):
        """
        执行更新操作（INSERT, UPDATE, DELETE等）
        
        Args:
            sql: SQL语句
            params: 参数（元组或字典）
            
        Returns:
            影响的行数
        """
        # 如果未连接，先连接
        if not self.conn:
            if not self.connect():
                return 0
        
        try:
            print(f"执行更新: {sql}")
            with self.conn.cursor() as cursor:
                affected_rows = cursor.execute(sql, params or ())
                self.conn.commit()
                print(f"更新成功，影响了 {affected_rows} 行")
                return affected_rows
        except Exception as e:
            self.conn.rollback()
            print(f"更新执行失败: {str(e)}")
            return 0
    
    def list_tables(self):
        """列出数据库中的所有表"""
        return self.query("SHOW TABLES")
    
    def describe_table(self, table_name):
        """查看表结构"""
        return self.query(f"DESCRIBE {table_name}")
    
    def __del__(self):
        """析构函数，确保连接关闭"""
        self.close()


# 提供一个简单的封装函数，便于导入使用
def query_db(sql, params=None):
    """
    快速查询数据库
    
    Args:
        sql: SQL查询语句
        params: 参数（可选）
        
    Returns:
        查询结果
    """
    db = SimpleDBQuery()
    results = db.query(sql, params)
    db.close()
    return results


# 使用示例
if __name__ == "__main__":
    # 创建查询对象
    db = SimpleDBQuery()
    
    try:
        # 处理命令行参数
        if len(sys.argv) > 1:
            sql = sys.argv[1]
            db.query(sql)
        else:
            # 交互式模式
            print("进入交互式查询模式，输入SQL语句执行查询，输入'exit'退出")
            while True:
                sql = input("\n请输入SQL查询 (输入'exit'退出): ")
                if sql.lower() in ('exit', 'quit', 'q'):
                    break
                
                if sql.strip():
                    db.query(sql)
    finally:
        # 关闭连接
        db.close() 